using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using VOL.Entity.DomainModels;
using SqlSugar;
using VOL.demo.IRepositories;

namespace VOL.demo.Repositories
{
    public partial class SalesReturnOrderModelRepository
    {
        /// <summary>
        /// 根据销售单号查询销售明细列表（包含已出库、未出库等信息）
        /// </summary>
        public async Task<List<SalesOrderDetailForReturnDto>> GetSalesOrderDetailsByCodeAsync(string shopOrderCode)
        {
            try
            {
                // 1. 先查询销售订单主表，获取销售订单ID
                var shopOrder = await DbContext.Queryable<shopordermodel>()
                    .Where(so => so.ShopOrderCode == shopOrderCode && so.IsDelete == 0)
                    .FirstAsync();

                if (shopOrder == null)
                {
                    return new List<SalesOrderDetailForReturnDto>();
                }

                // 2. 查询销售订单明细表，关联货品表获取货品信息
                var details = await DbContext.Queryable<shoporderdetailsmodel>()
                    .LeftJoin<productinformationmodel>((sod, g) => sod.GoodsId == g.Id)
                    .LeftJoin<unitmodel>((sod, g, u) => g.UnitId == u.Id)
                    .Where((sod, g, u) => sod.ShopOrderId == shopOrder.Id && sod.IsDelete == 0)
                    .Select((sod, g, u) => new SalesOrderDetailForReturnDto
                    {
                        ShopOrderDetailId = sod.Id,
                        GoodsId = sod.GoodsId,
                        GoodsCode = g.goodsCode,
                        GoodsName = g.goodsName,
                        SpecModel = g.specModel,
                        UnitId = g.UnitId,
                        UnitName = u.UnitName,
                        SalesPrice = sod.ShopOrderMoney,
                        SalesQuantity = sod.ShopOrderNum,
                        SalesAmount = sod.ShopOrderAllMoney,
                        // 默认值，后续计算
                        StockOutQuantity = 0,
                        UnstockOutQuantity = sod.ShopOrderNum
                    })
                    .ToListAsync();

                // 3. 计算已出库和未出库数量
                // 注意：这里假设有出库明细表，如果没有则根据销售订单的OutStatus字段来判断
                // 从shopordermodel表的OutStatus字段判断出库状态
                // OutStatus: 0-未出库, 1-部分出库, 2-全部出库
                if (shopOrder.OutStatus.HasValue)
                {
                    if (shopOrder.OutStatus == 2) // 全部出库
                    {
                        foreach (var detail in details)
                        {
                            detail.StockOutQuantity = detail.SalesQuantity;
                            detail.UnstockOutQuantity = 0;
                        }
                    }
                    else if (shopOrder.OutStatus == 1) // 部分出库
                    {
                        // 如果有出库明细表，可以从出库明细表查询具体的出库数量
                        // 这里暂时假设一半已出库
                        foreach (var detail in details)
                        {
                            detail.StockOutQuantity = detail.SalesQuantity / 2;
                            detail.UnstockOutQuantity = detail.SalesQuantity - detail.StockOutQuantity;
                        }
                    }
                    // 否则保持默认值（全部未出库）
                }

                // 4. 添加序号
                for (int i = 0; i < details.Count; i++)
                {
                    details[i].RowNumber = i + 1;
                }

                return details;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"查询销售明细失败: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// 生成退货单号
        /// </summary>
        public async Task<string> GenerateReturnOrderCodeAsync()
        {
            try
            {
                // 生成规则：TH + 年月日 + 4位流水号
                // 例如：TH20231201001
                var today = DateTime.Now;
                var datePrefix = today.ToString("yyyyMMdd");
                var codePrefix = $"TH{datePrefix}";

                // 查询当天最大的流水号
                var maxCode = await DbContext.Queryable<SalesReturnOrderModel>()
                    .Where(sro => sro.orderCode.StartsWith(codePrefix))
                    .OrderByDescending(sro => sro.orderCode)
                    .Select(sro => sro.orderCode)
                    .FirstAsync();

                int serialNumber = 1;
                if (!string.IsNullOrEmpty(maxCode) && maxCode.Length >= codePrefix.Length + 4)
                {
                    var serialStr = maxCode.Substring(codePrefix.Length);
                    if (int.TryParse(serialStr, out var lastSerial))
                    {
                        serialNumber = lastSerial + 1;
                    }
                }

                return $"{codePrefix}{serialNumber:D4}";
            }
            catch (Exception ex)
            {
                Console.WriteLine($"生成退货单号失败: {ex.Message}");
                // 如果查询失败，返回一个默认的单号
                return $"TH{DateTime.Now:yyyyMMdd}0001";
            }
        }

        /// <summary>
        /// 保存销售退货订单（包括主表和明细表）
        /// </summary>
        public async Task<(bool Success, string Message, long OrderId)> SaveSalesReturnOrderAsync(
            SalesReturnOrderModel mainData, 
            List<SalesReturnOrderItemModel> detailData)
        {
            try
            {
                // 开启事务
                await DbContext.Ado.BeginTranAsync();

                try
                {
                    // 1. 保存主表 - 使用原生SQL直接插入
                    string insertMainSql = @"
                        INSERT INTO salesreturnordermodel 
                        (orderCode, salesOrderItemCode, returnDate, supplierId, contact, contactPhone, 
                         deptId, userId, returnReason, remark, status, auditor, auditTime, 
                         isDel, createTime, modifier, modifyDate, customerName)
                        VALUES 
                        (@orderCode, @salesOrderItemCode, @returnDate, @supplierId, @contact, @contactPhone,
                         @deptId, @userId, @returnReason, @remark, @status, @auditor, @auditTime,
                         @isDel, @createTime, @modifier, @modifyDate, @customerName)";
                    
                    var parameters = new 
                    {
                        orderCode = mainData.orderCode,
                        salesOrderItemCode = mainData.salesOrderItemCode,
                        returnDate = mainData.returnDate,
                        supplierId = mainData.supplierId,
                        contact = mainData.contact,
                        contactPhone = mainData.contactPhone,
                        deptId = mainData.deptId,
                        userId = mainData.userId,
                        returnReason = mainData.returnReason,
                        remark = mainData.remark,
                        status = mainData.status,
                        auditor = mainData.auditor,
                        auditTime = mainData.auditTime,
                        isDel = mainData.isDel,
                        createTime = mainData.createTime,
                        modifier = mainData.modifier,
                        modifyDate = mainData.modifyDate,
                        customerName = mainData.customerName
                    };
                    
                    var orderId = await DbContext.Ado.SqlQuerySingleAsync<long>(
                        insertMainSql + "; SELECT LAST_INSERT_ID();", 
                        parameters);
                    
                    if (orderId <= 0)
                    {
                        await DbContext.Ado.RollbackTranAsync();
                        return (false, "保存主表失败", 0);
                    }

                    // 2. 保存明细表 - 使用原生SQL批量插入
                    if (detailData != null && detailData.Count > 0)
                    {
                        string insertDetailSql = @"
                            INSERT INTO salesreturnorderitemmodel 
                            (returnOrderId, purchaseOrderItemCode, goodsId, goodsCode, goodsName, specModel,
                             unitId, unitName, returnQuantity, purchasePrice, returnAmount, remarks,
                             isDel, creator, createTime, modifier, modifyDate, stockOutItemId)
                            VALUES 
                            (@returnOrderId, @purchaseOrderItemCode, @goodsId, @goodsCode, @goodsName, @specModel,
                             @unitId, @unitName, @returnQuantity, @purchasePrice, @returnAmount, @remarks,
                             @isDel, @creator, @createTime, @modifier, @modifyDate, @stockOutItemId)";
                        
                        int insertedCount = 0;
                        foreach (var detail in detailData)
                        {
                            detail.returnOrderId = orderId;
                            
                            var detailParams = new
                            {
                                returnOrderId = detail.returnOrderId,
                                purchaseOrderItemCode = detail.purchaseOrderItemCode,
                                goodsId = detail.goodsId,
                                goodsCode = detail.goodsCode,
                                goodsName = detail.goodsName,
                                specModel = detail.specModel,
                                unitId = detail.unitId,
                                unitName = detail.unitName,
                                returnQuantity = detail.returnQuantity,
                                purchasePrice = detail.purchasePrice,
                                returnAmount = detail.returnAmount,
                                remarks = detail.remarks,
                                isDel = detail.isDel,
                                creator = detail.creator,
                                createTime = detail.createTime,
                                modifier = detail.modifier,
                                modifyDate = detail.modifyDate,
                                stockOutItemId = detail.stockOutItemId
                            };
                            
                            int result = await DbContext.Ado.ExecuteCommandAsync(insertDetailSql, detailParams);
                            insertedCount += result;
                        }
                        
                        if (insertedCount != detailData.Count)
                        {
                            await DbContext.Ado.RollbackTranAsync();
                            return (false, $"保存明细表失败，应插入{detailData.Count}条，实际插入{insertedCount}条", 0);
                        }
                    }

                    // 3. 提交事务
                    await DbContext.Ado.CommitTranAsync();

                    return (true, "保存成功", orderId);
                }
                catch (Exception ex)
                {
                    await DbContext.Ado.RollbackTranAsync();
                    Console.WriteLine($"保存销售退货订单失败(事务内): {ex.Message}");
                    return (false, $"保存失败：{ex.Message}", 0);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"保存销售退货订单失败: {ex.Message}");
                return (false, $"保存失败：{ex.Message}", 0);
            }
        }

        /// <summary>
        /// 分页查询销售退货订单（带关联查询和计算字段）
        /// 使用两阶段查询策略避免字段名问题
        /// </summary>
        public async Task<(List<SalesReturnOrderModel> Data, int TotalCount)> GetSalesReturnOrderPageListAsync(
            int pageIndex, 
            int pageSize, 
            string orderCode = null, 
            int? status = null, 
            long? supplierId = null, 
            DateTime? returnDate = null)
        {
            // 第一阶段：主查询（简化，避免列名问题）
            var query = DbContext.Queryable<SalesReturnOrderModel>()
                .LeftJoin<suppliermodel>((sro, s) => sro.supplierId == s.Id)
                .LeftJoin<Sys_User>((sro, s, u) => sro.userId == u.User_Id)
                .LeftJoin<Sys_Department>((sro, s, u, d) => sro.deptId == SqlFunc.ToString(d.DepartmentId))
                .Where((sro, s, u, d) => sro.isDel == 0)
                .WhereIF(!string.IsNullOrEmpty(orderCode), (sro, s, u, d) => sro.orderCode.Contains(orderCode))
                .WhereIF(status.HasValue, (sro, s, u, d) => sro.status == status)
                .WhereIF(supplierId.HasValue, (sro, s, u, d) => sro.supplierId == supplierId)
                .WhereIF(returnDate.HasValue, (sro, s, u, d) => sro.returnDate.Value.Date == returnDate.Value.Date)
                .Select((sro, s, u, d) => new SalesReturnOrderModel
                {
                    Id = sro.Id,
                    orderCode = sro.orderCode,
                    // salesOrderItemCode = null,  // 暂时不查询，避免列名问题
                    returnDate = sro.returnDate,
                    supplierId = sro.supplierId,
                    contact = sro.contact,
                    contactPhone = sro.contactPhone,
                    deptId = sro.deptId,
                    userId = sro.userId,
                    returnReason = sro.returnReason,
                    remark = sro.remark,
                    status = sro.status,
                    auditor = sro.auditor,
                    auditTime = sro.auditTime,
                    createTime = sro.createTime,
                    modifier = sro.modifier,
                    modifyDate = sro.modifyDate,
                    customerName = sro.customerName,
                    supplierName = s.SupplierName,
                    userName = u.UserTrueName,
                    deptName = d.DepartmentName,
                    statusName = sro.status == 1 ? "草稿" :
                                sro.status == 2 ? "待审核" :
                                sro.status == 3 ? "驳回" :
                                sro.status == 4 ? "未入库" :
                                sro.status == 5 ? "部分入库" :
                                sro.status == 6 ? "已完成" : "未知"
                })
                .OrderByDescending(sro => sro.createTime);

            var totalCount = await query.CountAsync();
            var data = await query.ToPageListAsync(pageIndex, pageSize);
            
            // 第二阶段：补充查询 salesOrderItemCode（使用原始SQL，绕过ORM映射）
            if (data != null && data.Count > 0)
            {
                var ids = data.Select(d => d.Id).ToList();
                var codeSql = $"SELECT id, ` salesOrderItemCode` FROM salesreturnordermodel WHERE id IN ({string.Join(",", ids)})";
                var codeTable = await DbContext.Ado.GetDataTableAsync(codeSql);
                
                // 使用字典存储结果，避免DataTable.Select对列名空格的问题
                foreach (System.Data.DataRow row in codeTable.Rows)
                {
                    long id = Convert.ToInt64(row["id"]);
                    string code = row[1]?.ToString(); // 直接使用列索引，而不是列名
                    
                    var item = data.FirstOrDefault(d => d.Id == id);
                    if (item != null)
                    {
                        item.salesOrderItemCode = code;
                    }
                }
            }

            // 为每个订单计算汇总数据
            if (data != null && data.Count > 0)
            {
                foreach (var item in data)
                {
                    try
                    {
                        // 获取明细数据并计算汇总
                        var details = await DbContext.Queryable<SalesReturnOrderItemModel>()
                            .Where(d => d.returnOrderId == item.Id && d.isDel == 0)
                            .ToListAsync();

                        item.totalQuantity = details.Sum(d => d.returnQuantity ?? 0);
                        item.totalAmount = details.Sum(d => d.returnAmount ?? 0);
                        
                        // 处理退货货品字段，限制显示长度
                        var goods = details.Select(d => d.goodsName).Where(g => !string.IsNullOrEmpty(g)).Distinct().ToList();
                        
                        if (goods.Count > 3)
                        {
                            item.returnGoods = string.Join("、", goods.Take(3)) + $"等{goods.Count}项";
                        }
                        else
                        {
                            item.returnGoods = string.Join("、", goods);
                        }
                    }
                    catch (Exception ex)
                    {
                        // 如果明细表查询失败，设置默认值
                        item.totalQuantity = 0;
                        item.totalAmount = 0;
                        item.returnGoods = "明细数据查询失败";
                        
                        Console.WriteLine($"查询销售退货单明细失败: {ex.Message}");
                    }
                }
            }

            return (data, totalCount);
        }
    }
}
